package dao_service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import dao_pool.Pool;

import Protocol.threadStatus.ReplyMap;
import Protocol.threadStatus.ThreadStatus;
import i_dao.I_ThreadStatus_Dao;

public class ThreadStatusImp implements I_ThreadStatus_Dao {
	Pool        m_Pool = Pool.getInstance();
	@Override
	public boolean insertThreadStatus(ThreadStatus ts) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		
		//String sql="insert into detail_info (user_id,user_birthday,user_email,user_tele,user_description) values (?,?,?,?,?)";
		
		//PreparedStatement prstmt=conn.prepareStatement(sql);
		
		/*prstmt.setLong(1, new_user_id);
		prstmt.setString(2,new_user_birthday);
		prstmt.setString(3,new_email);
		prstmt.setString(4,new_user_tele);
		prstmt.setString(5,new_user_description);
		
		 prstmt.executeUpdate();*/
		
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="insert  into thread_status(thread_status_id,type,author_id,datetime,thread_type,content,isReplay ) " +
					" values ( ?,?,?,?,?,?,?)";
			PreparedStatement pt=conn.prepareStatement(sql);
			pt.setInt(1,ts.getId());
			pt.setString(2, ts.getType());
			pt.setInt(3, ts.getAuthor_id());
			pt.setDate(4, new java.sql.Date(ts.getDatetime().getTime()));
			pt.setString(5, ts.getThread_type());
			pt.setString(6, ts.getContent());
			pt.setString(7, ts.getIsReply());
			
			pt.executeUpdate();
			
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			m_Pool.freeConnection(conn);
		}
		
		return false;
	}

	@Override
	public boolean deleteThreadStatus(int threadStatus_id) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="delete from thread_status where thread_status_id='"+threadStatus_id+"'";
			statement.execute(sql);
			
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			m_Pool.freeConnection(conn);
		}
		
		return false;
	}

	@Override
	public boolean insertReplyMap(ReplyMap rm) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="insert  into reply_mapping(reply_map_id,parent_id,child_id) " +
					" values ( '"+rm.getMap_id()+"', '"+rm.getParent_id()+ "', '"+rm.getChild_id()+"')";
			statement.execute(sql);
			
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			m_Pool.freeConnection(conn);
		}
		
		return false;
	}

	@Override
	public boolean deleteRepyMap(int map_id) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="delete from reply_mapping where reply_map_id='"+map_id+"'";
			statement.execute(sql);
			
			return true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			m_Pool.freeConnection(conn);
		}
		
		return false;
	}

	@Override
	public ArrayList<ThreadStatus> getThreadStatusList(int author_id) {
		// TODO Auto-generated method stub
		ArrayList<ThreadStatus> result=new ArrayList<ThreadStatus>();
		 Connection conn=m_Pool.getConnection();	
			Statement statement = null;
			try {
				statement=conn.createStatement();
				String sql="select* from thread_status where author_id='"+author_id+"'";
				ResultSet rs=statement.executeQuery(sql);
				while(rs.next()){
					ThreadStatus ts=new ThreadStatus();
					ts.setId(rs.getInt("thread_status_id"));
					ts.setType(rs.getString("type"));
					ts.setAuthor_id(rs.getInt("author_id"));
					ts.setDatetime(rs.getDate("datetime"));
					ts.setThread_type(rs.getString("thread_type"));
					ts.setContent(rs.getString("content"));
					ts.setIsReply(rs.getString("isReplay"));
					result.add(ts);
				}	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				m_Pool.freeConnection(conn);
			}
		return result;
	}


	@Override
	public ArrayList<ThreadStatus> getOriginandReplyList(int thread_status_id) {
		// TODO Auto-generated method stub
		ArrayList<ThreadStatus> result=new ArrayList<ThreadStatus>();
		 Connection conn=m_Pool.getConnection();	
			Statement statement = null;
			try {
				statement=conn.createStatement();
				String sql="select * from thread_status where thread_status_id=(" +
						"select child_id from reply_mapping where parent_id='"+thread_status_id+"')";
				ResultSet rs=statement.executeQuery(sql);
				while(rs.next()){
					ThreadStatus ts=new ThreadStatus();
					ts.setId(rs.getInt("thread_status_id"));
					ts.setType(rs.getString("type"));
					ts.setAuthor_id(rs.getInt("author_id"));
					ts.setDatetime(rs.getDate("datetime"));
					ts.setThread_type(rs.getString("thread_type"));
					ts.setContent(rs.getString("content"));
					ts.setIsReply(rs.getString("isReplay"));
					result.add(ts);
				}	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				m_Pool.freeConnection(conn);
			}
		return result;
	}

	@Override
	public ArrayList<ThreadStatus> getNThreadStatusBefore(int count,Date beforeDate) {
		// TODO Auto-generated method stub
		ArrayList<ThreadStatus> result=new ArrayList<ThreadStatus>();
		 Connection conn=m_Pool.getConnection();	
			Statement statement = null;
			try {
				statement=conn.createStatement();
				String sql="select * from thread_status where datetime<'"+beforeDate+"'order by datetime" ;
				ResultSet rs=statement.executeQuery(sql);
				int cnt=0;
				while(rs.next()&&cnt<count){
					ThreadStatus ts=new ThreadStatus();
					ts.setId(rs.getInt("thread_status_id"));
					ts.setType(rs.getString("type"));
					ts.setAuthor_id(rs.getInt("author_id"));
					ts.setDatetime(rs.getDate("datetime"));
					ts.setThread_type(rs.getString("thread_type"));
					ts.setContent(rs.getString("content"));
					ts.setIsReply(rs.getString("isReplay"));
					result.add(ts);
					cnt++;
				}	
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				m_Pool.freeConnection(conn);
			}
		return result;
	}

	@Override
	public ThreadStatus getThreadStatus(int publisherId, Date publishTime) {
		// TODO Auto-generated method stub
		ThreadStatus result=new ThreadStatus();
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="select * from thread_status where author_id='"+publisherId+"'and datetime='"+publishTime+"'" ;
			ResultSet rs=statement.executeQuery(sql);
		while(rs.next()){
				result.setId(rs.getInt("thread_status_id"));
				result.setType(rs.getString("type"));
				result.setAuthor_id(rs.getInt("author_id"));
				result.setDatetime(rs.getDate("datetime"));
				result.setThread_type(rs.getString("thread_type"));
				result.setContent(rs.getString("content"));
				result.setIsReply(rs.getString("isReplay"));
		}
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			m_Pool.freeConnection(conn);
		}
	return result;
	}

	@Override
	public ArrayList<ThreadStatus> getTopThreadStatusList(int topCount) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ThreadStatus getThreadStatus(int threadStatus_id) {
		// TODO Auto-generated method stub
		 Connection conn=m_Pool.getConnection();	
			Statement statement = null;
			ThreadStatus ts = null;
			try {
				statement=conn.createStatement();
				String sql="select* from thread_status where thread_status_id='"+threadStatus_id+"'";
				ResultSet rs=statement.executeQuery(sql);
				if(rs.next()){
				    ts=new ThreadStatus();
					ts.setId(rs.getInt("thread_status_id"));
					ts.setType(rs.getString("type"));
					ts.setAuthor_id(rs.getInt("author_id"));
					ts.setDatetime(rs.getDate("datetime"));
					ts.setThread_type(rs.getString("thread_type"));
					ts.setContent(rs.getString("content"));
					ts.setIsReply(rs.getString("isReplay"));
				}	
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				m_Pool.freeConnection(conn);
			}
			
		return ts;
	}

}
